package com.whq.knowverywell.business.db;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.whq.knowverywell.business.db.helper.MainSQLiteOpenHelper;
import com.whq.knowverywell.business.model.User;
import com.whq.knowverywell.util.LogUtil;

/**
 * 用户记录表.
 *
 * @author wanghuaiqiang
 */
public class TblUserHandler {
    private static final String TAG = "TblUserHandler";
    
    public static final String TBL_USER 				= "tbl_user";
    
    private static final String COLUMN_USER_ID 			= "userId";
    private static final String COLUMN_GROUP_CODE 		= "groupCode";
    private static final String COLUMN_USER_NAME 		= "userName";
    private static final String COLUMN_PASSWORD 		= "password";
    private static final String COLUMN_CRYPT_KEY 		= "cryptKey";
    private static final String COLUMN_LOGIN_TIMESTAMP 	= "loginTimestamp";
    
    public static final String CREATE_TBL_USER_SQL = 
    				" CREATE TABLE " + TBL_USER + 
    				" (" + COLUMN_USER_ID + " INTEGER PRIMARY KEY autoincrement " + 
    				" , " + COLUMN_GROUP_CODE + " TEXT " +
    				" , " + COLUMN_USER_NAME + " TEXT " +
    				" , " + COLUMN_PASSWORD + " TEXT" +
					" , " + COLUMN_CRYPT_KEY + " TEXT" +
					" , " + COLUMN_LOGIN_TIMESTAMP + " INTEGER " +
					");";
    
    private MainSQLiteOpenHelper mainSQLiteOpenHelper = null;
    
    public TblUserHandler(Context context) {
        mainSQLiteOpenHelper = new MainSQLiteOpenHelper(context);
    }
    
    public void addOrUpdUser(User oneUser) {
        if ( oneUser == null) {
            return ;
        }
        LogUtil.i(TAG, "保存用户信息: " + oneUser.getUserId());
        
        User existObj = this.getOneUser(oneUser.getGroupCode(), oneUser.getUserName());
        if (existObj == null) {
        	this.addUser(oneUser);
        } else {
        	this.updUser(oneUser);
        }
    }
    
    private void addUser(User oneUser) {
        if (oneUser == null) {
            return ;
        }
        
        LogUtil.i(TAG, "Add user[groupCode:" + oneUser.getGroupCode() + "][userName:" + oneUser.getUserName() + "]");
        
        SQLiteDatabase m_oData = null;
        Cursor oCursor = null;
        try {
            m_oData = this.mainSQLiteOpenHelper.getWritableDatabase();
            String sql = "INSERT INTO " + TBL_USER +
            		" (" + COLUMN_GROUP_CODE + 
            		", " + COLUMN_USER_NAME + 
            		", " + COLUMN_PASSWORD +
            		", " + COLUMN_CRYPT_KEY +
            		", " + COLUMN_LOGIN_TIMESTAMP +
            		") " +
            		" VALUES " +
            		" (?" +
            		", ?" +
            		", ?" +
            		", ?" +
            		", ?" +
            		")";
        	Object[] paramsValue = new Object[]{oneUser.getGroupCode()
        			, oneUser.getUserName()
        			, oneUser.getPassword()
        			, oneUser.getCryptKey()
        			, oneUser.getLoginTimestamp()
        	        };
            m_oData.execSQL(sql, paramsValue);
            LogUtil.i(TAG, "sql：" + sql);
            LogUtil.i(TAG, "Add user[groupCode:" + oneUser.getGroupCode() + "][userName:" + oneUser.getUserName() + "]成功");
        } catch (Exception e) {
        	LogUtil.e(TAG, "Add user[groupCode:" + oneUser.getGroupCode() + "][userName:" + oneUser.getUserName() + "]失败：", e);
        } finally {
            if (oCursor != null) {
                oCursor.close();
                oCursor = null;
            }
            if (m_oData != null) {
                m_oData.close();
                m_oData = null;
            }
        }
    }
    
    private void updUser(User oneUser) {
        if ( oneUser == null) {
            return ;
        }
        
        LogUtil.i(TAG, "Upd user[groupCode:" + oneUser.getGroupCode() + "][userName:" + oneUser.getUserName() + "]");
        
        SQLiteDatabase m_oData = null;
        Cursor oCursor = null;
        try {
            m_oData = this.mainSQLiteOpenHelper.getWritableDatabase();
            String sql = "UPDATE " + TBL_USER +
            		" SET " + COLUMN_PASSWORD + "=?" +
            		", " + COLUMN_CRYPT_KEY + "=?" +
            		", " + COLUMN_LOGIN_TIMESTAMP + "=?" +
            		" WHERE " + COLUMN_GROUP_CODE + "=? " +
            		" AND " + COLUMN_USER_NAME + "=?";
					;
        	Object[] paramsValue = new Object[]{oneUser.getPassword()
        			, oneUser.getCryptKey()
        			, oneUser.getLoginTimestamp()
        			, oneUser.getGroupCode()
        			, oneUser.getUserName()
        	        };
            m_oData.execSQL(sql, paramsValue);
            LogUtil.i(TAG, "sql：" + sql);
            LogUtil.i(TAG, "Upd user[groupCode:" + oneUser.getGroupCode() + "][userName:" + oneUser.getUserName() + "]成功");
        } catch (Exception e) {
            LogUtil.e(TAG, "Upd user[groupCode:" + oneUser.getGroupCode() + "][userName:" + oneUser.getUserName() + "]失败：", e);
        } finally {
            if (oCursor != null) {
                oCursor.close();
                oCursor = null;
            }
            if (m_oData != null) {
                m_oData.close();
                m_oData = null;
            }
        }
    }
    
    public void delUser(int userId) {
        LogUtil.i(TAG, "删除用户信息: " + userId);
        
        SQLiteDatabase m_oData = null;
        Cursor oCursor = null;
        try {
            m_oData = this.mainSQLiteOpenHelper.getWritableDatabase();
            String sql = "DELETE FROM " + TBL_USER + " WHERE " + COLUMN_USER_ID + "=?";
        	Object[] paramsValue = new Object[]{userId};
            m_oData.execSQL(sql, paramsValue);
            LogUtil.i(TAG, "sql：" + sql);
            LogUtil.i(TAG, "删除用户信息: " + userId + " 成功");
        } catch (Exception e) {
            LogUtil.e(TAG, "删除用户信息: " + userId + "发生异常：", e);
        } finally {
            if (oCursor != null) {
                oCursor.close();
                oCursor = null;
            }
            if (m_oData != null) {
                m_oData.close();
                m_oData = null;
            }
        }
    }
    
    public User getLastLoginUser() {
    	LogUtil.i(TAG, "获取最后登录的用户信息");
        
        SQLiteDatabase m_oData = null;
        Cursor oCursor = null;
        try {
        	m_oData = this.mainSQLiteOpenHelper.getReadableDatabase(); 
            String sql = "SELECT * FROM " + TBL_USER + 
            		" ORDER BY " + COLUMN_LOGIN_TIMESTAMP + " DESC";
            LogUtil.i(TAG, "sql：" + sql);
            oCursor = m_oData.rawQuery(sql, new String[]{});
            if (oCursor.moveToNext()) {
            	return getRecordFromCursor(oCursor);
            }
        } catch (Exception e) {
            LogUtil.e(TAG, "获取最后登录的用户信息发生异常：", e);
        } finally {
            if (oCursor != null) {
                oCursor.close();
                oCursor = null;
            }
            if (m_oData != null) {
                m_oData.close();
                m_oData = null;
            }
        }
		return null;
    }
    
    private User getOneUser(String groupCode, String userName) {
        LogUtil.i(TAG, "获取一个用户[" + groupCode + ", " + userName + "]信息");
        
        if ( (groupCode == null) || (userName == null) ) {
        	return null;
        }
        
        SQLiteDatabase m_oData = null;
        Cursor oCursor = null;
        try {
        	m_oData = this.mainSQLiteOpenHelper.getReadableDatabase(); 
            String sql = "SELECT * FROM " + TBL_USER + 
            		" WHERE " + COLUMN_GROUP_CODE + "=? AND " + COLUMN_USER_NAME + "=?";
            LogUtil.i(TAG, "sql：" + sql);
            oCursor = m_oData.rawQuery(sql, new String[]{groupCode, userName});
            if (oCursor.moveToNext()) {
            	return getRecordFromCursor(oCursor);
            }
        } catch (Exception e) {
            LogUtil.e(TAG, "获取一个用户[" + groupCode + ", " + userName + "]信息发生异常：", e);
        } finally {
            if (oCursor != null) {
                oCursor.close();
                oCursor = null;
            }
            if (m_oData != null) {
                m_oData.close();
                m_oData = null;
            }
        }
		return null;
    }
    
    public List<User> getAllUsers() {
        LogUtil.i(TAG, "获取所有用户信息");
        List<User> retList = new ArrayList<User>(0);
        SQLiteDatabase m_oData = null;
        Cursor oCursor = null;
        try {
        	m_oData = this.mainSQLiteOpenHelper.getReadableDatabase(); 
            String sql = "SELECT * FROM " + TBL_USER;
            LogUtil.i(TAG, "sql：" + sql);
            oCursor = m_oData.rawQuery(sql, new String[]{});
            while (oCursor.moveToNext()) {
            	retList.add(getRecordFromCursor(oCursor));
            }
        } catch (Exception e) {
            LogUtil.e(TAG, "获取所有用户信息发生异常：", e);
        } finally {
            if (oCursor != null) {
                oCursor.close();
                oCursor = null;
            }
            if (m_oData != null) {
                m_oData.close();
                m_oData = null;
            }
        }
		return retList;
    }
    
    public void delAllUsers() {
        LogUtil.i(TAG, "删除所有用户信息");
        SQLiteDatabase m_oData = null;
        try {
        	m_oData = this.mainSQLiteOpenHelper.getWritableDatabase();
            String sql  = "DELETE FROM " + TBL_USER;
            m_oData.execSQL(sql);
            LogUtil.i(TAG, "sql：" + sql);
            LogUtil.i(TAG, "删除所有用户信息 成功");
            
        } catch (Exception e) {
            LogUtil.e(TAG, "删除所有用户信息发生异常：", e);
        } finally {
            if (m_oData != null) {
                m_oData.close();
                m_oData = null;
            }
        }
    }
    
    private User getRecordFromCursor(Cursor oCursor) {
    	User ret = new User();
    	try {
	        ret.setUserId(oCursor.getInt(oCursor.getColumnIndex(COLUMN_USER_ID)));
    	} catch (Exception e) {
    	}
    	
    	try {
			ret.setGroupCode(oCursor.getString(oCursor.getColumnIndex(COLUMN_GROUP_CODE)));
		} catch (Exception e) {
		}
    	
    	try {
    		ret.setUserName(oCursor.getString(oCursor.getColumnIndex(COLUMN_USER_NAME)));
    	} catch (Exception e) {
    	}
    	
    	try {
			ret.setPassword(oCursor.getString(oCursor.getColumnIndex(COLUMN_PASSWORD)));
		} catch (Exception e) {
		}
    	
		try {
			ret.setCryptKey(oCursor.getString(oCursor.getColumnIndex(COLUMN_CRYPT_KEY)));
		} catch (Exception e) {
		}
		
		try {
			ret.setLoginTimestamp(oCursor.getLong(oCursor.getColumnIndex(COLUMN_LOGIN_TIMESTAMP)));
		} catch (Exception e) {
		}
    	return ret;
    }
    
}
